package com.dp.service.companymanage;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.JsonPage;
import com.dp.service.impl3.BaseImpl;
import com.dp.util.DataMap;
import com.dp.vo.NewsSystemPopumsg;
import com.dp.vo.TSpcode;

@Service
@Transactional
public class PopumsgServiceImpl extends BaseImpl {

	/**
	 * Find all the NewsSystemPopumsg Entity
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<NewsSystemPopumsg>
	 */
	public List<NewsSystemPopumsg> findAllPopumsgEntity(DataMap paramMap, Integer firstrow,
			Integer maxrow){
		JsonPage jpage=(JsonPage) paramMap.get("jpage");
		//NewsSystemPopumsg popumsg=(NewsSystemPopumsg) paramMap.get("popumsg");
		String begintimepage = (String) paramMap.get("begintimepage");
		String endtimepage = (String) paramMap.get("endtimepage");
		String userids=(String)paramMap.get("userids");
		StringBuffer sqlS = new StringBuffer("SELECT n FROM NewsSystemPopumsg n WHERE 1=1 and msgcontenttype=2 ");
		if(userids!=null){
			sqlS.append(" AND msgcreater in ("+userids+")");
		}
		/*if(popumsg!=null){
			if(popumsg.getMsgcreater()!=null&& popumsg.getMsgcreater().toString().trim().length() > 0){
				sqlS.append(" AND n.msgcreater like '%"
						+ popumsg.getMsgcreater().toString().trim() + "%'");
			}
		}*/
		if(begintimepage!=null&& begintimepage.toString().trim().length() > 0){
			sqlS.append(" AND n.msgcreatetime > '"
					+ begintimepage.toString().trim() + "'");
		}	
		if(endtimepage!=null&& endtimepage.toString().trim().length() > 0){
			sqlS.append(" AND n.msgcreatetime < '"
					+ endtimepage.toString().trim() + "'");
		}	
		Object role_id = paramMap.get("role_id");
		Object user_id = paramMap.get("user_id");
		if(StringIsNotNull(role_id)&&StringIsNotNull(user_id))
	    {
			sqlS.append(" AND n.msgcreater = '"
					+ user_id + "'");
	    }
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				sqlS.append(" order by n."+jpage.getSidx()+" "+jpage.getSord());
			}
		}
		List<NewsSystemPopumsg> queryList=new ArrayList<NewsSystemPopumsg>();
		try {
			Query query = em.createQuery(sqlS.toString());
			if (firstrow != null) {
				query.setFirstResult(firstrow);
			}
			if (maxrow != null) {
				query.setMaxResults(maxrow);
			}
			queryList = query.getResultList();
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}
	
	/**
	 * 发作业/通知 信息总记录数
	 * @param hashMap
	 * @return Integer
	 */
	public Integer findAllPopumsgEntityCount(DataMap paramMap){
		//NewsSystemPopumsg popumsg=(NewsSystemPopumsg) paramMap.get("popumsg");
		String begintimepage = (String) paramMap.get("begintimepage");
		String endtimepage = (String) paramMap.get("endtimepage");
		Integer iCount = 0; 
		String userids=(String)paramMap.get("userids");
		StringBuffer sqlS = new StringBuffer("SELECT count(n.msgid) FROM NewsSystemPopumsg n WHERE 1=1 and msgcontenttype=2 ");
		if(userids!=null){
			sqlS.append(" AND msgcreater in ("+userids+")");
		}
		/*if(popumsg!=null){
			if(popumsg.getMsgcreater()!=null&& popumsg.getMsgcreater().toString().trim().length() > 0){
				sqlS.append(" AND n.msgcreater like '%"
					+ popumsg.getMsgcreater().toString().trim() + "%'");
			}
		}*/
		if(begintimepage!=null&& begintimepage.toString().trim().length() > 0){
			sqlS.append(" AND n.msgcreatetime > '"
				+ begintimepage.toString().trim() + "'");
		}	
		if(endtimepage!=null&& endtimepage.toString().trim().length() > 0){
			sqlS.append(" AND n.msgcreatetime < '"
				+ endtimepage.toString().trim() + "'");
		}	
		Object role_id = paramMap.get("role_id");
		Object user_id = paramMap.get("user_id");
		if(StringIsNotNull(role_id)&&StringIsNotNull(user_id))
	    {
			sqlS.append(" AND n.msgcreater = '"
					+ user_id + "'");
	    }
		Query query=null;
		try {
			query = em.createQuery(sqlS.toString());
			iCount = Integer.valueOf(query.getSingleResult().toString());
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return iCount;
	}
	
	/**
	 * 增加
	 * @param popumsg
	 * @param groupruleids
	 * @return
	 */
	public Boolean addPopumsg(NewsSystemPopumsg popumsg,String groupruleids) {
		boolean resultb=true;
		try {
//			this.emr.merge(popumsg);
			this.em.persist(popumsg);
//			this.init();
//			this.sql="insert into news_systempopumsg (msgmoretext,msgaction,flag,msgcreater,msgcreatetime,msgcontenttype,msginvalidtime) values (?,?,?,?,?,2,?)";
//			this.prep=this.conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
//			prep.setString(1, popumsg.getMsgmoretext());
//			prep.setInt(2, popumsg.getMsgaction());
//			prep.setInt(3, popumsg.getFlag());
//			prep.setString(4, popumsg.getMsgcreater());
//			prep.setString(5, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(popumsg.getMsgcreatetime()));
//			prep.setString(6, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(popumsg.getMsginvalidtime()));
//			prep.executeUpdate();
//			this.rs1=prep.getGeneratedKeys();
//			if (rs1.next()) {
//				popumsg.setMsgid(rs1.getInt(1));
//			}
			if(groupruleids!=null&&!"".equals(groupruleids)){
				String [] groupidarys=groupruleids.split(",");
				sql = "insert into news_systempopumsggroup (msgid,groupruleid) values (?,(select groupid from t_spcode_device where spcode=?))";
				
Query	query=em.createNativeQuery(sql);
				
				
					
				for(String groupid:groupidarys){
					//增加消息和用户组间的关系
//					prep=conn.prepareStatement(sql);
//					prep.setInt(1, popumsg.getMsgid());
//					prep.setInt(2, Integer.parseInt(groupid));
//					prep.executeUpdate();
					query.setParameter(1, popumsg.getMsgid());
					query.setParameter(2, groupid);
					query.executeUpdate();
				}
			}
//			conn.commit(); /
		} catch (Exception e) {
			logger.error("addPopumsg fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
			}
		return resultb;
	}

	/**
	 * 修改
	 * @param popumsg
	 * @param groupruleids
	 * @return
	 */
	public Boolean updatePopumsg(NewsSystemPopumsg popumsg,String groupruleids) {
		boolean resultb=true;
		try {
			this.em.merge(popumsg);
			this.em.flush();
//			this.init();
//			this.sql="update news_systempopumsg set msgmoretext=?,msgaction=?,flag=?,msgcreatetime=?,msginvalidtime=? where msgid=?";
//			this.prep=this.conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
//			prep.setString(1, popumsg.getMsgmoretext());
//			prep.setInt(2, popumsg.getMsgaction());
//			prep.setInt(3, popumsg.getFlag());
//			prep.setString(4, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(popumsg.getMsgcreatetime()));
//			prep.setString(5, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(popumsg.getMsginvalidtime()));
//			prep.setInt(6, popumsg.getMsgid());
//			prep.executeUpdate();
//			this.rs1=prep.getGeneratedKeys();
//			if (rs1.next()) {
//				popumsg.setMsgid(rs1.getInt(1));
//			}
			//删除老的消息和用户组间的关系
			sql = "delete from news_systempopumsggroup where msgid="+popumsg.getMsgid();
//			prep=conn.prepareStatement(sql);
//			prep.setInt(1, popumsg.getMsgid());
//			prep.executeUpdate();
			this.em.createNativeQuery(sql).executeUpdate();
			if(groupruleids!=null&&!"".equals(groupruleids)){
				sql = "insert into news_systempopumsggroup (msgid,groupruleid) values (?,(select groupid from t_spcode_device where spcode=?))";
//				prep=conn.prepareStatement(sql);
				String [] groupidarys=groupruleids.split(",");
				Query	query=em.createNativeQuery(sql);
				
				for(String groupid:groupidarys){
					//增加消息和用户组间的关系
//					prep=conn.prepareStatement(sql);
//					prep.setInt(1, popumsg.getMsgid());
//					prep.setInt(2, Integer.parseInt(groupid));
//					prep.executeUpdate();
					query.setParameter(1, popumsg.getMsgid());
					query.setParameter(2, groupid);
					query.executeUpdate();
				}
			}
//			conn.commit(); 
		} catch (Exception e) {
			logger.error("updatePopumsg fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}
	
	/**
	 * 删除
	 * @param msgid
	 * @return
	 */
	public Boolean deleteNewsSystemPopumsg(String ids) {
		Boolean resultb=true;
		try {
//			this.init();
			if(ids!=null&&!"".equals(ids)){
				String [] idarys=ids.split(",");
				for(String id:idarys){
					this.sql="select msgid from news_clientgetmsg where msgid="+id;
					
					if(!(this.getOneInt()>0)){
						this.sql="delete from news_systempopumsggroup where msgid="+id;
						this.em.createNativeQuery(sql).executeUpdate();
						
						this.sql="delete from news_systempopumsg where msgid="+id;
						this.em.createNativeQuery(sql).executeUpdate();
					}else{
						if(idarys.length==1){
							resultb=null;
						}
					}
				}
			}else{
				resultb=false;
			}
			
			
		} catch (Exception e) {
			logger.error("deleteNewsSystemPopumsg fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;	}
		return resultb;
	}
	
	
	/**
	 * @param msgid
	 * @return groupruleid|grouprulename
	 */
	public List getGroupBymsgid(int msgid) {
		List<String> groupruleid_namesList=new ArrayList<String> ();
		String sql = "select grou.groupruleid,grou.grouprulename groupruleid_names from news_group grou "
				+ " join news_systempopumsggroup msggroup on msggroup.groupruleid= grou.groupruleid"
				+ " join news_systempopumsg msg on msg.msgid=msggroup.msgid"
				+ " where msg.msgid="+msgid;
		
		try {
//			this.init();
//			prep=conn.prepareStatement(sql);
//			prep.setInt(1, msgid);
//			rs1=prep.executeQuery();
			List<Object[]> temp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:temp)
			{
				groupruleid_namesList.add(this.getString(o[0])+"|"+this.getString(o[1]));
			}
		
		} catch (Exception e) {
			logger.error("deleteNewsSystemPopumsg fail !!!" + e.getMessage());
			e.printStackTrace();
		} 
		return groupruleid_namesList;
	}
	
	
	/**
	 * @param msgid
	 * 
	 */
	public List getSpcodeBymsgid(int msgid) {
		sql = "select tsd.spcode from news_systempopumsggroup nsg,news_systempopumsg msg,t_spcode_device tsd where msg.msgid=nsg.msgid and tsd.groupid=nsg.groupruleid and msg.msgid="+msgid+"";
		try {
			return this.em.createNativeQuery(this.sql).getResultList();
			//return queryALLResultSet(querySql);
		} catch (Exception e) {
			logger.info(" getGroupBymsgid fail !!!" +e.getMessage());
			return null;
		}
	}
	
	public boolean invalidateE(String ids) {
		Boolean resultb=true;
		try {
//			this.init();
			if(ids!=null&&!"".equals(ids)){
				String [] idarys=ids.split(",");
				SimpleDateFormat fmt_temp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String DateS=fmt_temp.format(new java.util.Date());
				this.sql="update news_systempopumsg set flag=2,msginvalidtime='"+DateS+"' where msgid=?";
//				prep=conn.prepareStatement(sql);
				for(String id:idarys){
//					prep.setString(1, DateS);
//					prep.setString(2, id);
//					prep.addBatch();
					this.sql="update news_systempopumsg set flag=2,msginvalidtime='"+DateS+"' where msgid="+id;
					this.em.createNativeQuery(sql).executeUpdate();
				}
//				prep.executeBatch();
			}else{
				resultb=false;
			}
			
//			conn.commit(); 
		} catch (Exception e) {
			logger.error("invalidateE fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}

	
	public  ArrayList getAllFirstSpcodes(String user_id) {
		String querySql="select ts.spcode,ts.spname from t_spcode ts,t_spcode_device tsd where ts.spcode=tsd.spcode";
		if(user_id!=null){
			querySql+=" and ts.spcode=(select te.sp_code from t_employee te, t_user tu where tu.employee_id=te.employee_id and tu.user_id='" + user_id + "')";
		}else{
			querySql+=" and upspcode is null";
		}
		try{
//			return queryALLResultSet(querySql);
			return null;
		} catch (Exception e) {
			logger.info(" getAllFirstChs fail !!!" +e.getMessage());
			return null;
		}
	}
	
	public List<TSpcode> findAllSubOrganization(DataMap hashMap)
	{
		String spcode=(String)hashMap.get("spcode");
		String spcodeS=(String)hashMap.get("spcodeS");
		StringBuffer sqlS = new StringBuffer("SELECT n FROM TSpcode n ");
		if(spcode!=null){
			sqlS.append("where n.spcode='"+spcode+"'");
		}else if(spcodeS!=null){
			sqlS.append("where n.spcode in ("+spcodeS+")");
		}
		sqlS.append(" order by n.id ");
		List<TSpcode> l=new ArrayList<TSpcode>();
		try {
			Query query = em.createQuery(sqlS.toString());
			logger.info("findAllSubOrganization excute sql:"+sqlS.toString());
			l = query.getResultList();
			
		} catch (Exception e) {
			logger.error("findAllSubOrganization error!!!"+e.getMessage());
			e.printStackTrace();
		}
		
		return l;
	}
}
